#!/home/python/.virtualenvs/open/bin/python

import sys
sys.path.append('./')
sys.path.insert(0, "../")

import os
if not os.getenv('DJANGO_SETTINGS_MODULE'):
    os.environ['DJANGO_SETTINGS_MODULE'] = 'equity_mall.settings.dev'

import django
django.setup()

import pymysql, requests
from django.conf import settings
from django.db.models import Q, F, Func, Count
from decimal import Decimal

from cashbonus.models import *
from merchants.models import Merchant, MerchantUser, MerchantBill
from vouchers.models import *
from orders.models import OrderInfo


def wash_activity_data(cash_activity_id):
    cash_activity = CashActivity.objects.get(id=cash_activity_id)
    if cash_activity.activity_type in [1, 2, 3]:
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor()
        sql = f'''SELECT cash_activity_level_id,SUM(amount) AS amount__sum FROM tb_cash_activity_data WHERE cash_activity_id={cash_activity_id} AND state IN (1,2) GROUP BY cash_activity_level_id;'''
        cursor.execute(sql)
        rows = cursor.fetchall()
        dict_info = {}
        for row in rows:
            dict_info[row[0]] = {}
            dict_info[row[0]]['take_amount'] = row[1]
        sql = f'''SELECT cash_activity_level_id,SUM(amount) AS amount__sum FROM tb_cash_activity_data WHERE cash_activity_id={cash_activity_id} AND state IN (2) GROUP BY cash_activity_level_id;'''
        cursor.execute(sql)
        rows = cursor.fetchall()
        for row in rows:
            dict_info[row[0]]['receive_amount'] = row[1]
        for key, value in dict_info.items():
            cashactivitylevel = CashActivityLevel.objects.filter(id=key).update(
                take_amount=value['take_amount'],
                receive_amount=value['receive_amount'] if 'receive_amount' in value else 0,
                remain_amount=F('max_amount')-value['take_amount']
            )
        level_list = ['prov_id', 'city_id', 'district_id', 'branch_id']
        for level_str in level_list:
            sql = f'''SELECT {level_str},SUM(amount),SUM(CASE state WHEN 2 THEN amount ELSE 0 END) AS receive___sum FROM (SELECT * FROM (SELECT amount,level_id,state FROM tb_cash_activity_data WHERE cash_activity_id={cash_activity_id} AND state IN (1,2)) AS a LEFT JOIN tb_u_level AS b ON a.level_id=b.id) AS c GROUP BY {level_str};'''
            cursor.execute(sql)
            rows = cursor.fetchall()
            for row in rows:
                if level_str == 'prov_id':
                    CashActivity.objects.filter(id=cash_activity_id).update(
                        distributed_amount=row[2],
                        take_amount=row[1],
                        remain_amount=F('max_amount')-row[1],
                    )
                cashactivitylevel = CashActivityLevel.objects.filter(level_id=row[0], cash_activity_id=cash_activity_id).update(
                    p_take_amount=row[1],
                    p_receive_amount=row[2],
                    p_remain_amount=F('p_max_amount')-row[1]
                )
        cursor.close()
        conn.close()
        print(dict_info)
    else:
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor()
        sql = f'''SELECT cash_activity_level_id,SUM(amount) AS amount__sum FROM tb_cash_activity_data WHERE cash_activity_id={cash_activity_id} AND state IN (1,2) GROUP BY cash_activity_level_id;'''
        cursor.execute(sql)
        rows = cursor.fetchall()
        dict_info = {}
        for row in rows:
            dict_info[row[0]] = {}
            dict_info[row[0]]['take_amount'] = row[1]
        sql = f'''SELECT cash_activity_level_id,SUM(amount) AS amount__sum FROM tb_cash_activity_data WHERE cash_activity_id={cash_activity_id} AND state IN (2) GROUP BY cash_activity_level_id;'''
        cursor.execute(sql)
        rows = cursor.fetchall()
        for row in rows:
            dict_info[row[0]]['receive_amount'] = row[1]
        for key, value in dict_info.items():
            cashactivitylevel = CashActivityLevel.objects.filter(id=key).update(
                take_amount=value['take_amount'],
                receive_amount=value['receive_amount'],
                max_amount=value['take_amount'],
            )
        level_list = ['prov_id', 'city_id', 'district_id', 'branch_id']
        for level_str in level_list:
            sql = f'''SELECT {level_str},SUM(amount),SUM(CASE state WHEN 2 THEN amount ELSE 0 END) AS receive___sum FROM (SELECT * FROM (SELECT amount,level_id,state FROM tb_cash_activity_data WHERE cash_activity_id={cash_activity_id} AND state IN (1,2)) AS a LEFT JOIN tb_u_level AS b ON a.level_id=b.id) AS c GROUP BY {level_str};'''
            cursor.execute(sql)
            rows = cursor.fetchall()
            for row in rows:
                if level_str == 'prov_id':
                    CashActivity.objects.filter(id=cash_activity_id).update(
                        distributed_amount=row[2],
                        take_amount=row[1],
                        remain_amount=F('max_amount')-row[1],
                    )
                cashactivitylevel = CashActivityLevel.objects.filter(level_id=row[0], cash_activity_id=cash_activity_id).update(
                    p_take_amount=row[1],
                    p_receive_amount=row[2],
                    p_max_amount=row[1]
                )
        cursor.close()
        conn.close()
        print(dict_info)


def wash_merchant_user():
    merchant_set = Merchant.objects.filter(merchantuser_id__isnull=True)
    for merchant in merchant_set:
        mobile = merchant.managermobile
        merchantuser, create = MerchantUser.objects.get_or_create(mobile=mobile, defaults={"mobile": mobile})
        merchant.merchantuser_id = merchantuser.id
        merchant.save()
        print(merchant.id)


def wash_alidiscountcouponlevel():
    queryset = AliDiscountCouponLevel.objects.filter(activity_id=22985, level__level__in=[4,3,2,1])
    for query in queryset:
        level = query.level
        activity_id = query.activity_id
        if level.level == 4:
            district = level.parent_id
            prov = level.parent.parent.parent_id
            city = level.parent.parent_id
            instance = AliDiscountCouponLevel.objects.filter(level_id=district, activity_id=activity_id).first()
            if not instance:
                print(query.id)
                print(prov)
            AliDiscountCouponLevelRelation.objects.get_or_create(parent=instance, child=query, defaults={"parent": instance, "child": query})
            instance = AliDiscountCouponLevel.objects.filter(level_id=city, activity_id=activity_id).first()
            if not instance:
                print(query.id)
                print(prov)
            AliDiscountCouponLevelRelation.objects.get_or_create(parent=instance, child=query, defaults={"parent": instance, "child": query})
            instance = AliDiscountCouponLevel.objects.filter(level_id=prov, activity_id=activity_id).first()
            if not instance:
                print(query.id)
                print(prov)
            AliDiscountCouponLevelRelation.objects.get_or_create(parent=instance, child=query, defaults={"parent": instance, "child": query})
        elif level.level == 3:
            prov = level.parent.parent_id
            city = level.parent_id
            instance = AliDiscountCouponLevel.objects.filter(level_id=city, activity_id=activity_id).first()
            if not instance:
                print(query.id)
                print(prov)
            AliDiscountCouponLevelRelation.objects.get_or_create(parent=instance, child=query, defaults={"parent": instance, "child": query})
            instance = AliDiscountCouponLevel.objects.filter(level_id=prov, activity_id=activity_id).first()
            if not instance:
                print(query.id)
                print(prov)
            AliDiscountCouponLevelRelation.objects.get_or_create(parent=instance, child=query, defaults={"parent": instance, "child": query})
        elif level.level == 2:
            prov = level.parent_id
            instance = AliDiscountCouponLevel.objects.filter(level_id=prov, activity_id=activity_id).first()
            if not instance:
                print(query.id)
                print(prov)
            AliDiscountCouponLevelRelation.objects.get_or_create(parent=instance, child=query, defaults={"parent": instance, "child": query})
    print(len(queryset))


def wash_alidiscountcouponlevel1():
    queryset = AliDiscountCouponLevel.objects.filter(activity_id=18317, level__level=4)
    for query in queryset:
        max_number = query.max_number
        # remain_number = query.remain_number
        take_number = query.take_number
        # new_max_number = max_number / 2
        new_remain_number = max_number - take_number
        query.remain_number = new_remain_number
        # query.max_number = new_max_number
        # query.p_max_number = new_max_number
        query.p_remain_number = new_remain_number
        query.save()
    # queryset = AliDiscountCouponInfo.objects.filter(alidiscountcoupon_id=18317).exclude(status=13)
    # conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
    #                        port=settings.DATABASES['default']['PORT'],
    #                        database=settings.DATABASES['default']['NAME'],
    #                        user=settings.DATABASES['default']['USER'],
    #                        password=settings.DATABASES['default']['PASSWORD'])
    # cursor = conn.cursor()
    # sql = f'''SELECT alidiscountcoupon_level_id,COUNT(alidiscountcoupon_level_id)AS count FROM tb_ali_discount_coupon_info WHERE alidiscountcoupon_id=18317 AND status!=13 AND status!=0 GROUP BY alidiscountcoupon_level_id;'''
    # cursor.execute(sql)
    # rows = cursor.fetchall()
    # for row in rows:
    #     query = AliDiscountCouponLevel.objects.get(id=row[0])
    #     query.receive_number = row[1]
    #     query.p_receive_number = row[1]
    #     query.save()


def wash_alidiscountcoupon_data(alidiscountcoupon_id):
    alidiscountcoupon = AliDiscountCoupon.objects.get(id=alidiscountcoupon_id)
    # if alidiscountcoupon.coupon_way == 1:
    # conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
    #                        port=settings.DATABASES['default']['PORT'],
    #                        database=settings.DATABASES['default']['NAME'],
    #                        user=settings.DATABASES['default']['USER'],
    #                        password=settings.DATABASES['default']['PASSWORD'])
    # cursor = conn.cursor()
    # sql = f'''SELECT cash_activity_level_id,SUM(amount) AS amount__sum FROM tb_cash_activity_data WHERE cash_activity_id={cash_activity_id} AND state IN (1,2) GROUP BY cash_activity_level_id;'''
    # cursor.execute(sql)
    # rows = cursor.fetchall()
    query_set = AliDiscountCouponInfo.objects.exclude(status=13).filter(alidiscountcoupon_id=alidiscountcoupon_id).values('alidiscountcoupon_level_id').annotate(Count('alidiscountcoupon_level_id'))
    alidiscountcoupon_id_take = {}
    for query in query_set:
        alidiscountcoupon_id_take[query['alidiscountcoupon_level_id']] = {}
        alidiscountcoupon_id_take[query['alidiscountcoupon_level_id']]['take_count'] = query['alidiscountcoupon_level_id__count']
    query_set = AliDiscountCouponInfo.objects.exclude(status__in=[13, 0]).filter(alidiscountcoupon_id=alidiscountcoupon_id).values('alidiscountcoupon_level_id').annotate(Count('alidiscountcoupon_level_id'))
    for query in query_set:
        alidiscountcoupon_id_take[query['alidiscountcoupon_level_id']]['receive_count'] = query['alidiscountcoupon_level_id__count']
    query_set = AliDiscountCouponInfo.objects.filter(alidiscountcoupon_id=alidiscountcoupon_id, status=7).values('alidiscountcoupon_level_id').annotate(Count('alidiscountcoupon_level_id'))
    for query in query_set:
        alidiscountcoupon_id_take[query['alidiscountcoupon_level_id']]['use_count'] = query['alidiscountcoupon_level_id__count']

    alidiscountcoupon_level_set = AliDiscountCouponLevel.objects.filter(activity_id=alidiscountcoupon_id).order_by('level__level')
    for alidiscountcoupon_level in alidiscountcoupon_level_set:
        # if alidiscountcoupon_level.level.level == 1:
        #     if alidiscountcoupon_level.id in alidiscountcoupon_id_take:
        #         value = alidiscountcoupon_id_take[alidiscountcoupon_level.id]
        #         receive_count = value['receive_count'] if 'receive_count' in value else 0
        #         take_count = value['take_count'] if 'take_count' in value else 0
        #         use_count = value['use_count'] if 'use_count' in value else 0
        #         alidiscountcoupon_level.p_receive_number = receive_count
        #         alidiscountcoupon_level.receive_number = receive_count
        #         alidiscountcoupon_level.p_take_number = take_count
        #         alidiscountcoupon_level.take_number = take_count
        #         alidiscountcoupon_level.p_use_number = use_count
        #         alidiscountcoupon_level.use_number = use_count
        #         alidiscountcoupon_level.p_remain_number = alidiscountcoupon_level.max_number - take_count
        #         alidiscountcoupon_level.remain_number = alidiscountcoupon_level.max_number - take_count
        #         alidiscountcoupon_level.p_max_number = alidiscountcoupon_level.max_number
        #         alidiscountcoupon_level.save()
        #     else:
        #         alidiscountcoupon_level.p_receive_number = 0
        #         alidiscountcoupon_level.receive_number = 0
        #         alidiscountcoupon_level.p_take_number = 0
        #         alidiscountcoupon_level.take_number = 0
        #         alidiscountcoupon_level.p_use_number = 0
        #         alidiscountcoupon_level.use_number = 0
        #         alidiscountcoupon_level.p_remain_number = alidiscountcoupon_level.max_number
        #         alidiscountcoupon_level.remain_number = alidiscountcoupon_level.max_number
        #         alidiscountcoupon_level.p_max_number = alidiscountcoupon_level.max_number
        #         alidiscountcoupon_level.save()
        # elif alidiscountcoupon_level.level.level == 2:
        if alidiscountcoupon_level.id in alidiscountcoupon_id_take:
            value = alidiscountcoupon_id_take[alidiscountcoupon_level.id]
            receive_count = value['receive_count'] if 'receive_count' in value else 0
            take_count = value['take_count'] if 'take_count' in value else 0
            use_count = value['use_count'] if 'use_count' in value else 0
            alidiscountcoupon_level.p_receive_number = receive_count
            alidiscountcoupon_level.receive_number = receive_count
            alidiscountcoupon_level.p_take_number = take_count
            alidiscountcoupon_level.take_number = take_count
            alidiscountcoupon_level.p_use_number = use_count
            alidiscountcoupon_level.use_number = use_count
            alidiscountcoupon_level.p_remain_number = alidiscountcoupon_level.max_number - take_count
            alidiscountcoupon_level.remain_number = alidiscountcoupon_level.max_number - take_count
            alidiscountcoupon_level.p_max_number = alidiscountcoupon_level.max_number
            alidiscountcoupon_level.save()
            relation_set = AliDiscountCouponLevelRelation.objects.filter(child=alidiscountcoupon_level.id)
            for relation in relation_set:
                parent = relation.parent
                parent.p_receive_number = parent.p_receive_number + alidiscountcoupon_level.receive_number
                parent.p_take_number = parent.p_take_number + alidiscountcoupon_level.take_number
                parent.p_use_number = parent.p_use_number + alidiscountcoupon_level.use_number
                parent.p_remain_number = parent.p_remain_number + alidiscountcoupon_level.remain_number
                parent.p_max_number = parent.p_max_number + alidiscountcoupon_level.max_number
                parent.save()
        else:
            alidiscountcoupon_level.p_receive_number = 0
            alidiscountcoupon_level.receive_number = 0
            alidiscountcoupon_level.p_take_number = 0
            alidiscountcoupon_level.take_number = 0
            alidiscountcoupon_level.p_use_number = 0
            alidiscountcoupon_level.use_number = 0
            alidiscountcoupon_level.p_remain_number = alidiscountcoupon_level.max_number
            alidiscountcoupon_level.remain_number = alidiscountcoupon_level.max_number
            alidiscountcoupon_level.p_max_number = alidiscountcoupon_level.max_number
            alidiscountcoupon_level.save()
            relation_set = AliDiscountCouponLevelRelation.objects.filter(child=alidiscountcoupon_level.id)
            for relation in relation_set:
                parent = relation.parent
                parent.p_receive_number = parent.p_receive_number + alidiscountcoupon_level.receive_number
                parent.p_take_number = parent.p_take_number + alidiscountcoupon_level.take_number
                parent.p_use_number = parent.p_use_number + alidiscountcoupon_level.use_number
                parent.p_remain_number = parent.p_remain_number + alidiscountcoupon_level.remain_number
                parent.p_max_number = parent.p_max_number + alidiscountcoupon_level.max_number
                parent.save()

    # for key, value in alidiscountcoupon_id_take.items():
    #     alidiscountcoupon_level = AliDiscountCouponLevel.objects.get(id=key)
    #     if alidiscountcoupon_level.level.level == 1:
    #         receive_count = value['receive_count'] if 'receive_count' in value else 0
    #         take_count = value['take_count'] if 'take_count' in value else 0
    #         use_count = value['use_count'] if 'use_count' in value else 0
    #         alidiscountcoupon_level.p_receive_number = receive_count
    #         alidiscountcoupon_level.receive_number = receive_count
    #         alidiscountcoupon_level.p_take_number = take_count
    #         alidiscountcoupon_level.take_number = take_count
    #         alidiscountcoupon_level.p_use_number = use_count
    #         alidiscountcoupon_level.use_number = use_count
    #         alidiscountcoupon_level.p_remain_number = alidiscountcoupon_level.max_number - take_count
    #         alidiscountcoupon_level.remain_number = alidiscountcoupon_level.max_number - take_count
    #         alidiscountcoupon_level.save()
    #     if alidiscountcoupon_level.level.level == 3:
    #         pass
    #     if alidiscountcoupon_level.level.level == 2:
    #         pass
    #     if alidiscountcoupon_level.level.level == 1:
    #         pass


def wash_alidiscountcoupon_data_gongxiang(alidiscountcoupon_id):
    query_set = AliDiscountCouponInfo.objects.exclude(status=13).filter(alidiscountcoupon_id=alidiscountcoupon_id).values('alidiscountcoupon_level_id').annotate(Count('alidiscountcoupon_level_id'))
    alidiscountcoupon_id_take = {}
    for query in query_set:
        alidiscountcoupon_id_take[query['alidiscountcoupon_level_id']] = {}
        alidiscountcoupon_id_take[query['alidiscountcoupon_level_id']]['take_count'] = query['alidiscountcoupon_level_id__count']
    query_set = AliDiscountCouponInfo.objects.filter(alidiscountcoupon_id=alidiscountcoupon_id, status=7).values('alidiscountcoupon_level_id').annotate(Count('alidiscountcoupon_level_id'))
    for query in query_set:
        alidiscountcoupon_id_take[query['alidiscountcoupon_level_id']]['use_count'] = query['alidiscountcoupon_level_id__count']
    alidiscountcoupon_level_set = AliDiscountCouponLevel.objects.filter(activity_id=alidiscountcoupon_id).order_by('level__level')
    for alidiscountcoupon_level in alidiscountcoupon_level_set:
        if alidiscountcoupon_level.id in alidiscountcoupon_id_take:
            value = alidiscountcoupon_id_take[alidiscountcoupon_level.id]
            take_count = value['take_count'] if 'take_count' in value else 0
            use_count = value['use_count'] if 'use_count' in value else 0
            alidiscountcoupon_level.p_receive_number = take_count
            alidiscountcoupon_level.receive_number = take_count
            alidiscountcoupon_level.p_take_number = take_count
            alidiscountcoupon_level.take_number = take_count
            alidiscountcoupon_level.max_number = take_count
            alidiscountcoupon_level.p_max_number = take_count
            alidiscountcoupon_level.use_number = use_count
            alidiscountcoupon_level.p_use_number = use_count
            alidiscountcoupon_level.save()
        else:
            alidiscountcoupon_level.p_receive_number = 0
            alidiscountcoupon_level.receive_number = 0
            alidiscountcoupon_level.p_take_number = 0
            alidiscountcoupon_level.take_number = 0
            alidiscountcoupon_level.max_number = 0
            alidiscountcoupon_level.p_max_number = 0
            alidiscountcoupon_level.use_number = 0
            alidiscountcoupon_level.p_use_number = 0
            alidiscountcoupon_level.save()
    for alidiscountcoupon_level in alidiscountcoupon_level_set:
        relation_set = AliDiscountCouponLevelRelation.objects.filter(child=alidiscountcoupon_level.id)
        for relation in relation_set:
            parent = relation.parent
            parent.p_receive_number = parent.p_receive_number + alidiscountcoupon_level.receive_number
            parent.p_take_number = parent.p_take_number + alidiscountcoupon_level.take_number
            parent.p_max_number = parent.p_max_number + alidiscountcoupon_level.max_number
            parent.p_use_number = parent.p_use_number + alidiscountcoupon_level.use_number
            parent.save()


    # query_set = AliDiscountCouponInfo.objects.exclude(status=13).filter(alidiscountcoupon_id=alidiscountcoupon_id).values('alidiscountcoupon_level_id').annotate(Count('alidiscountcoupon_level_id'))
    # for query in query_set:
    #     alidiscountcoupon_level = AliDiscountCouponLevel.objects.get(id=query['alidiscountcoupon_level_id'])
    #     alidiscountcoupon_level.max_number = query['alidiscountcoupon_level_id__count']
    #     alidiscountcoupon_level.take_number = query['alidiscountcoupon_level_id__count']
    #     alidiscountcoupon_level.receive_number = query['alidiscountcoupon_level_id__count']
    #     alidiscountcoupon_level.p_max_number = query['alidiscountcoupon_level_id__count']
    #     alidiscountcoupon_level.p_take_number = query['alidiscountcoupon_level_id__count']
    #     alidiscountcoupon_level.p_receive_number = query['alidiscountcoupon_level_id__count']
    #     alidiscountcoupon_level.save()
    # alidiscountcoupon_level_set = AliDiscountCouponLevel.objects.filter(activity_id=alidiscountcoupon_id)
    # for alidiscountcoupon_level in alidiscountcoupon_level_set:
    #     if alidiscountcoupon_level.level.level == 3:
    #         relation_set = AliDiscountCouponLevelRelation.objects.filter(child=alidiscountcoupon_level.id)
    #         for relation in relation_set:
    #             parent = relation.parent
    #             parent.p_receive_number = parent.p_receive_number + alidiscountcoupon_level.receive_number
    #             parent.p_take_number = parent.p_take_number + alidiscountcoupon_level.take_number
    #             parent.p_max_number = parent.p_max_number + alidiscountcoupon_level.max_number
    #             parent.save()


def wash_freight():
    queryset = OrderInfo.objects.exclude(state__in=[13, 1]).filter(order_species=2, id__gte=5732428, freight=0, payment_time__isnull=False)
    url = 'https://manage.gdwxyf.com/internal/searchorders.do'
    body = {
        "orderId": '',
        "start": '2024-01-01 14:30:10',
        "end": '2024-02-02 23:59:59',
        "branchId": '2',
        "account": 'gdyouzheng',
        "channel": 'all',
        "orderStatus": 'all',
        "orderType": 'all',
        "payType": 'all',
        "agentId": 'all',
        "shopId": '',
    }
    for query in queryset:
        try:
            body['orderId'] = query.orderNum
            result = requests.post(url=url, data=body).json()['showList']
            if result:
                commission = result[0]['commission']
                query.freight = Decimal(str(commission))
                query.save()
                print(f'{query.id}成功')
            else:
                print(f'{query.id}找不到')
        except:
            print(f'{query.id}失败')
    print(len(queryset))


def wasg_orderd():
    queryset = OrderInfo.objects.filter(id=7713112)
    for order in queryset:
        order_id = order.order_id
        orderNum = order.orderNum
        order.order_id = order_id
        order.orderNum = orderNum
        order.save()



if __name__ == '__main__':
    pass
    wash_activity_data(1270)
    # wasg_orderd()
    # wash_alidiscountcoupon_data(22985)
    # wash_alidiscountcouponlevel()
    # wash_alidiscountcoupon_data_gongxiang(24112)
    # wash_freight()
    # ceshi_decimal()